/*
 * Projet Java / UML -- Trombinoscope - Février / Mars 2012
 * Virginie Messe -- William Huber
 */
package outils;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import mvc_model.Promotion;

/**
 *
 * @author Virginie Messe & William Huber
 */
public class PromotionDAO extends DAO<Promotion>{
    
    
    /**
     * 
     * @param obj L'objet à ajouter à la base de données <br/>
     * 
     * requires <br/>
     *      
     * ensures <br/>
     *   // La table en question n'est pas vide
     */
    @Override
    public Promotion create(Promotion obj) {
            Connection connect = this.connect;
            String sql = "INSERT INTO t_promotion (minAccessLevel, classe, anneeScolaire, email) VALUES (?, ?, ?, ?)";
            PreparedStatement ps;
        try {
            ps = (PreparedStatement) connect.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, obj.getMinAccessLevel());
            ps.setString(2, obj.getClasse());
            ps.setString(3, obj.getAnneeScolaire());
            ps.setString(4, obj.getEmail());
            ps.executeUpdate();
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(PromotionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
            return obj;
	}
	
	/**
         * 
         * @param id L'identifiant de l'objet que l'on cherche dans la base <br/>
         * 
         * requires <br/>
         *      
         * ensures <br/>
         *  
         */
	@Override
	public Promotion find(int id) {
		Promotion lang = new Promotion();
		try {
            ResultSet result = this.connect
                                   .createStatement(
                                            	ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                                ResultSet.CONCUR_UPDATABLE
                                             ).executeQuery(
                                                "SELECT * FROM t_promotion WHERE idPromotion = " + id
                                             );
            if(result.first())
            		lang = new Promotion(
                                                id,
                                                result.getString("classe"),
                                                result.getString("anneeScolaire"),
                                                result.getInt("minAccessLevel"),
                                                result.getString("email")
                                    );
            
		    } catch (SQLException e) {
		            e.printStackTrace();
		    }
		   return lang;

	}
	
	@Override
	public Promotion update(Promotion obj) {
	Connection connect = this.connect;
            String sql = "UPDATE t_promotion SET minAccessLevel = ?, classe = ?, anneeScolaire = ?, email = ? WHERE idPromotion = ?";
        try {
            PreparedStatement ps = connect.prepareStatement(sql);
            ps.setInt(1, obj.getMinAccessLevel());
            ps.setString(2, obj.getClasse());
            ps.setString(3, obj.getAnneeScolaire());
            ps.setString(4, obj.getEmail());
            ps.setInt(5, obj.getId());
            ps.executeUpdate();
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(PersonneDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
	    
	    
	    return obj;
	}

        @Override
	public void delete(int idPromotion) {
			Connection connect = this.connect;
                String sql = "DELETE FROM t_promotion WHERE idPromotion = ?";
        try {
            PreparedStatement ps = connect.prepareStatement(sql);
            ps.setInt(1, idPromotion);
            ps.executeUpdate();
            ps.close();
          } catch (SQLException ex) {
            Logger.getLogger(PersonneDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
	}
        
        @Override
        public int getMax() {
            int res = 0;
            try {
			ResultSet result = this.connect
                                    .createStatement(
                                    		ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                    		ResultSet.CONCUR_UPDATABLE
                                    ).executeQuery(
                                    		"SELECT MAX('idPromotion') as id"
                                    );
			if(result.first()){
				res = result.getInt("idPromotion");
			}
	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
	    return res;
	}
        
        public ArrayList<Promotion> listerPromotions() {
            ArrayList resAL = new ArrayList<Promotion>();
            Connection connect_local = null;
            Statement statement = null;
            ResultSet res = null;
            
            Promotion promo_une_par_une = new Promotion();
            
            try {
                connect_local = this.connect;
                statement = connect_local.createStatement();
                res = statement.executeQuery("SELECT * FROM t_promotion ;");
                
                // On se place sur la première ligne du ResultSet
                //res.first();
                
                // Parcours du ResultSet
                while (res.next())
                {
                    promo_une_par_une = new Promotion(
                                                res.getInt("idPromotion"),
                                                res.getString("classe"),
                                                res.getString("anneeScolaire"),
                                                res.getInt("minAccessLevel"),
                                                res.getString("email")
                                            );
                    resAL.add(promo_une_par_une);
                }

	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
            
            return resAL;
        }
}
